﻿Imports System.Configuration
Imports System.Data.SqlClient
Imports SIGI.Common.BE

Public Class AppointmentDAC

#Region "<< -SearchByDate- >>"
    Public Shared Function SearchByDate(ByVal pDate As Date, ByVal pUserName As String) As List(Of AppointmentBE)
        Dim wConnStr As String = String.Empty
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wApt As AppointmentBE = Nothing
        Dim wAptList As List(Of AppointmentBE) = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand() With {.CommandType = CommandType.StoredProcedure,
                                             .CommandText = "Citas_sPorFecha", .Connection = wSqlConn}

            wSqlCmd.Parameters.Add("@Fecha", SqlDbType.Date).Value = pDate
            wSqlCmd.Parameters.Add("@Usuario", SqlDbType.NVarChar, 150).Value = pUserName

            wSqlConn.Open()

            wAptList = New List(Of AppointmentBE)
            wReader = wSqlCmd.ExecuteReader()
            While (wReader.Read)
                wApt = New AppointmentBE
                wApt.Id = Convert.ToInt32(wReader("Id"))
                wApt.Subject = Convert.ToString(wReader("Asunto"))
                wApt.Description = Convert.ToString(wReader("Descripcion"))
                wApt.DateStart = Convert.ToDateTime(wReader("FechaInicio"))
                wApt.DateEnd = Convert.ToDateTime(wReader("FechaFin"))
                wApt.Importance = Convert.ToInt32(wReader("Importancia"))
                wApt.Warning = Convert.ToInt32(wReader("Aviso"))

                wAptList.Add(wApt)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wAptList
    End Function
#End Region

#Region "<< -Search- >>"
    Public Shared Function Search(ByVal pUserName As String) As List(Of AppointmentBE)
        Dim wConnStr As String = String.Empty
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wApt As AppointmentBE = Nothing
        Dim wAptList As List(Of AppointmentBE) = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand() With {.CommandType = CommandType.StoredProcedure,
                                             .CommandText = "Citas_s", .Connection = wSqlConn}

            wSqlCmd.Parameters.Add("@IdUsuario", SqlDbType.NVarChar, 150).Value = pUserName
            wSqlConn.Open()

            wAptList = New List(Of AppointmentBE)
            wReader = wSqlCmd.ExecuteReader()
            While (wReader.Read)
                wApt = New AppointmentBE
                wApt.Id = Convert.ToInt32(wReader("Id"))
                wApt.Subject = Convert.ToString(wReader("Asunto"))
                wApt.Description = Convert.ToString(wReader("Descripcion"))
                wApt.DateStart = Convert.ToDateTime(wReader("FechaInicio"))
                wApt.DateEnd = Convert.ToDateTime(wReader("FechaFin"))
                wApt.Importance = Convert.ToInt32(wReader("Importancia"))
                wApt.Warning = Convert.ToInt32(wReader("Aviso"))

                wAptList.Add(wApt)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wAptList
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Function Insert(ByVal pApt As AppointmentBE, ByVal pUserName As String) As Integer
        Dim wConnStr As String = String.Empty
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wId As Integer = 0

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand() With {.CommandType = CommandType.StoredProcedure,
                                             .CommandText = "Citas_i", .Connection = wSqlConn}

            wSqlCmd.Parameters.Add("@Asunto", SqlDbType.NVarChar, 100).Value = pApt.Subject
            wSqlCmd.Parameters.Add("@Descripcion", SqlDbType.NVarChar, 500).Value = pApt.Description
            wSqlCmd.Parameters.Add("@FechaInicio", SqlDbType.DateTime).Value = pApt.DateStart
            wSqlCmd.Parameters.Add("@FechaFin", SqlDbType.DateTime).Value = pApt.DateEnd
            wSqlCmd.Parameters.Add("@Importancia", SqlDbType.Int).Value = pApt.Importance
            wSqlCmd.Parameters.Add("@Aviso", SqlDbType.Int).Value = pApt.Warning
            wSqlCmd.Parameters.Add("@Usuario", SqlDbType.NVarChar, 150).Value = pUserName

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            If wReader.Read() Then
                wId = Convert.ToInt32(wReader("Id"))
            End If

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wId
    End Function
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(ByVal pApt As AppointmentBE)
        Dim wConnStr As String = String.Empty
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand() With {.CommandType = CommandType.StoredProcedure,
                                             .CommandText = "Citas_u", .Connection = wSqlConn}

            wSqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = pApt.Id
            wSqlCmd.Parameters.Add("@Asunto", SqlDbType.NVarChar, 100).Value = pApt.Subject
            wSqlCmd.Parameters.Add("@Descripcion", SqlDbType.NVarChar, 500).Value = pApt.Description
            wSqlCmd.Parameters.Add("@FechaInicio", SqlDbType.DateTime).Value = pApt.DateStart
            wSqlCmd.Parameters.Add("@FechaFin", SqlDbType.DateTime).Value = pApt.DateEnd
            wSqlCmd.Parameters.Add("@Importancia", SqlDbType.Int).Value = pApt.Importance
            wSqlCmd.Parameters.Add("@Aviso", SqlDbType.Int).Value = pApt.Warning

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Delete- >>"
    Public Shared Sub Delete(ByVal pAptId As Integer)
        Dim wConnStr As String = String.Empty
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand() With {.CommandType = CommandType.StoredProcedure,
                                             .CommandText = "Citas_d", .Connection = wSqlConn}

            wSqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value = pAptId

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

End Class
